package com.xuanzheng.company.dao.impl;

import com.xuanzheng.company.dao.CompanyEmployeeDao;
import com.xuanzheng.company.entity.CompanyEmployee;
import com.xuanzheng.company.util.DBUtil;
import com.xuanzheng.company.util.IdGenerator;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CompanyEmployeeDaoImpl implements CompanyEmployeeDao {

    @Override
    public boolean addApplication(CompanyEmployee companyEmployee) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean result = false;

        try {
            conn = DBUtil.getConnection();
            String sql = "INSERT INTO company_employee (id, company_id, user_id, status, reason) " +
                    "VALUES (?, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);

            // 生成唯一ID
            String id = IdGenerator.generateId();
            companyEmployee.setId(id);

            pstmt.setString(1, companyEmployee.getId());
            pstmt.setString(2, companyEmployee.getCompanyId());
            pstmt.setString(3, companyEmployee.getUserId());
            pstmt.setInt(4, 0); // 默认状态为待审核
            pstmt.setString(5, companyEmployee.getReason());

            int affectedRows = pstmt.executeUpdate();
            result = (affectedRows > 0);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }

        return result;
    }

    @Override
    public boolean updateApplicationStatus(String id, int status, String employeeNumber, String position, BigDecimal baseSalary, String rejectReason) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean result = false;

        try {
            conn = DBUtil.getConnection();
            String sql;
            if (status == 1) { // 通过
                sql = "UPDATE company_employee SET status = ?, employee_number = ?, position = ?, base_salary = ? WHERE id = ?";
                pstmt = conn.prepareStatement(sql);
                pstmt.setInt(1, status);
                pstmt.setString(2, employeeNumber);
                pstmt.setString(3, position);
                pstmt.setBigDecimal(4, baseSalary);
                pstmt.setString(5, id);
            } else if (status == 2) { // 拒绝
                sql = "UPDATE company_employee SET status = ?, reject_reason = ? WHERE id = ?";
                pstmt = conn.prepareStatement(sql);
                pstmt.setInt(1, status);
                pstmt.setString(2, rejectReason);
                pstmt.setString(3, id);
            } else {
                return false;
            }

            int affectedRows = pstmt.executeUpdate();
            
            // 如果申请通过，则更新用户表中的公司ID和员工编号
            if (status == 1 && affectedRows > 0) {
                CompanyEmployee application = getApplicationById(id);
                if (application != null) {
                    PreparedStatement pstmt2 = null;
                    try {
                        String updateUserSql = "UPDATE user SET company_id = ?, employee_number = ? WHERE id = ?";
                        pstmt2 = conn.prepareStatement(updateUserSql);
                        pstmt2.setString(1, application.getCompanyId());
                        pstmt2.setString(2, employeeNumber);
                        pstmt2.setString(3, application.getUserId());
                        pstmt2.executeUpdate();
                    } finally {
                        if (pstmt2 != null) {
                            pstmt2.close();
                        }
                    }
                }
            }
            
            result = (affectedRows > 0);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }

        return result;
    }

    @Override
    public CompanyEmployee getApplicationById(String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        CompanyEmployee companyEmployee = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT ce.*, u.name as user_name, u.phone as user_phone, c.name as company_name " +
                    "FROM company_employee ce " +
                    "JOIN user u ON ce.user_id = u.id " +
                    "JOIN company c ON ce.company_id = c.id " +
                    "WHERE ce.id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                companyEmployee = mapResultSetToCompanyEmployee(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return companyEmployee;
    }

    @Override
    public List<CompanyEmployee> getApplicationsByUserId(String userId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<CompanyEmployee> applications = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT ce.*, u.name as user_name, u.phone as user_phone, c.name as company_name " +
                    "FROM company_employee ce " +
                    "JOIN user u ON ce.user_id = u.id " +
                    "JOIN company c ON ce.company_id = c.id " +
                    "WHERE ce.user_id = ? " +
                    "ORDER BY ce.create_time DESC";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                CompanyEmployee companyEmployee = mapResultSetToCompanyEmployee(rs);
                applications.add(companyEmployee);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return applications;
    }

    @Override
    public List<CompanyEmployee> getApplicationsByCompanyId(String companyId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<CompanyEmployee> applications = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT ce.*, u.name as user_name, u.phone as user_phone, c.name as company_name " +
                    "FROM company_employee ce " +
                    "JOIN user u ON ce.user_id = u.id " +
                    "JOIN company c ON ce.company_id = c.id " +
                    "WHERE ce.company_id = ? " +
                    "ORDER BY ce.create_time DESC";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, companyId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                CompanyEmployee companyEmployee = mapResultSetToCompanyEmployee(rs);
                applications.add(companyEmployee);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return applications;
    }

    @Override
    public List<CompanyEmployee> getApplicationsByCompanyIdAndStatus(String companyId, int status) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<CompanyEmployee> applications = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT ce.*, u.name as user_name, u.phone as user_phone, c.name as company_name " +
                    "FROM company_employee ce " +
                    "JOIN user u ON ce.user_id = u.id " +
                    "JOIN company c ON ce.company_id = c.id " +
                    "WHERE ce.company_id = ? AND ce.status = ? " +
                    "ORDER BY ce.create_time DESC";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, companyId);
            pstmt.setInt(2, status);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                CompanyEmployee companyEmployee = mapResultSetToCompanyEmployee(rs);
                applications.add(companyEmployee);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return applications;
    }

    @Override
    public CompanyEmployee getLatestApplicationByUserId(String userId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        CompanyEmployee companyEmployee = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT ce.*, u.name as user_name, u.phone as user_phone, c.name as company_name " +
                    "FROM company_employee ce " +
                    "JOIN user u ON ce.user_id = u.id " +
                    "JOIN company c ON ce.company_id = c.id " +
                    "WHERE ce.user_id = ? " +
                    "ORDER BY ce.create_time DESC LIMIT 1";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                companyEmployee = mapResultSetToCompanyEmployee(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return companyEmployee;
    }

    @Override
    public CompanyEmployee getApplicationByUserIdAndCompanyId(String userId, String companyId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        CompanyEmployee companyEmployee = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT ce.*, u.name as user_name, u.phone as user_phone, c.name as company_name " +
                    "FROM company_employee ce " +
                    "JOIN user u ON ce.user_id = u.id " +
                    "JOIN company c ON ce.company_id = c.id " +
                    "WHERE ce.user_id = ? AND ce.company_id = ? " +
                    "ORDER BY ce.create_time DESC LIMIT 1";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userId);
            pstmt.setString(2, companyId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                companyEmployee = mapResultSetToCompanyEmployee(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return companyEmployee;
    }

    /**
     * 根据用户ID和公司ID获取员工记录（已通过的申请）
     * @param userId 用户ID
     * @param companyId 公司ID
     * @return 员工记录
     */
    public CompanyEmployee getCompanyEmployeeByUserIdAndCompanyId(String userId, String companyId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        CompanyEmployee companyEmployee = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT ce.*, u.name as user_name, u.phone as user_phone, c.name as company_name " +
                    "FROM company_employee ce " +
                    "JOIN user u ON ce.user_id = u.id " +
                    "JOIN company c ON ce.company_id = c.id " +
                    "WHERE ce.user_id = ? AND ce.company_id = ? AND ce.status = 1 " +
                    "ORDER BY ce.create_time DESC LIMIT 1";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userId);
            pstmt.setString(2, companyId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                companyEmployee = mapResultSetToCompanyEmployee(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return companyEmployee;
    }
    
    @Override
    public boolean isUserInCompany(String userId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean result = false;

        try {
            conn = DBUtil.getConnection();
            // 首先检查用户是否在公司中
            String sql = "SELECT * FROM user WHERE id = ? AND company_id IS NOT NULL";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userId);
            rs = pstmt.executeQuery();

            result = rs.next();
            
            // 如果用户不在公司中，检查是否有离职申请
            if (!result) {
                DBUtil.close(null, pstmt, rs);
                
                String resignSql = "SELECT ce.* FROM company_employee ce " +
                                  "WHERE ce.user_id = ? AND ce.status = 3"; // 状态3表示待审核离职
                pstmt = conn.prepareStatement(resignSql);
                pstmt.setString(1, userId);
                rs = pstmt.executeQuery();
                
                result = rs.next(); // 如果有离职申请，也视为在公司中
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return result;
    }

    @Override
    public boolean updateApplicationStatusForReapply(String id, String reason) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean result = false;

        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE company_employee SET status = 0, reason = ?, reject_reason = NULL, update_time = NOW() WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, reason);
            pstmt.setString(2, id);

            int affectedRows = pstmt.executeUpdate();
            result = (affectedRows > 0);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }

        return result;
    }

    @Override
    public List<CompanyEmployee> getEmployeesByCompanyId(String companyId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<CompanyEmployee> employees = new ArrayList<>();

        try {
            System.out.println("CompanyEmployeeDaoImpl: 开始获取公司员工列表, 公司ID=" + companyId);
            conn = DBUtil.getConnection();
            String sql = "SELECT ce.*, u.name as user_name, u.phone as user_phone, c.name as company_name " +
                    "FROM company_employee ce " +
                    "JOIN user u ON ce.user_id = u.id " +
                    "JOIN company c ON ce.company_id = c.id " +
                    "WHERE ce.company_id = ? AND ce.status = 1 " +
                    "ORDER BY ce.employee_number ASC";
            System.out.println("CompanyEmployeeDaoImpl: SQL查询=" + sql);
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, companyId);
            
            System.out.println("CompanyEmployeeDaoImpl: 执行查询");
            rs = pstmt.executeQuery();

            int count = 0;
            while (rs.next()) {
                CompanyEmployee employee = mapResultSetToCompanyEmployee(rs);
                employees.add(employee);
                count++;
                
                // 打印前5个员工的基本信息
                if (count <= 5) {
                    System.out.println("CompanyEmployeeDaoImpl: 员工" + count + ": ID=" + employee.getId() + 
                            ", 姓名=" + employee.getUserName() + ", 电话=" + employee.getUserPhone());
                }
            }
            System.out.println("CompanyEmployeeDaoImpl: 共找到" + count + "名员工");
        } catch (SQLException e) {
            System.err.println("CompanyEmployeeDaoImpl: 获取员工列表时发生SQL异常: " + e.getMessage());
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return employees;
    }
    
    @Override
    public List<CompanyEmployee> searchEmployees(String companyId, String keyword) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<CompanyEmployee> employees = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT ce.*, u.name as user_name, u.phone as user_phone, c.name as company_name " +
                    "FROM company_employee ce " +
                    "JOIN user u ON ce.user_id = u.id " +
                    "JOIN company c ON ce.company_id = c.id " +
                    "WHERE ce.company_id = ? AND ce.status = 1 " +
                    "AND (ce.id LIKE ? OR u.name LIKE ? OR u.phone LIKE ? OR ce.position LIKE ? OR ce.employee_number LIKE ?) " +
                    "ORDER BY ce.employee_number ASC";
            pstmt = conn.prepareStatement(sql);
            String searchPattern = "%" + keyword + "%";
            pstmt.setString(1, companyId);
            pstmt.setString(2, searchPattern);
            pstmt.setString(3, searchPattern);
            pstmt.setString(4, searchPattern);
            pstmt.setString(5, searchPattern);
            pstmt.setString(6, searchPattern);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                CompanyEmployee employee = mapResultSetToCompanyEmployee(rs);
                employees.add(employee);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return employees;
    }
    
    @Override
    public CompanyEmployee getEmployeeDetail(String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        CompanyEmployee employee = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT ce.*, u.name as user_name, u.phone as user_phone, u.age, u.email, u.address, " +
                    "u.id_card, c.name as company_name " +
                    "FROM company_employee ce " +
                    "JOIN user u ON ce.user_id = u.id " +
                    "JOIN company c ON ce.company_id = c.id " +
                    "WHERE ce.id = ? AND ce.status = 1";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                employee = mapResultSetToCompanyEmployee(rs);
                // 额外的用户信息
                employee.setUserAge(rs.getInt("age"));
                employee.setUserEmail(rs.getString("email"));
                employee.setUserAddress(rs.getString("address"));
                employee.setUserIdCard(rs.getString("id_card"));
                
                // 打印日志，查看身份证信息是否正确获取
                System.out.println("获取到的身份证信息: " + rs.getString("id_card"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return employee;
    }
    
    @Override
    public boolean updateEmployeeInfo(String id, String position, BigDecimal baseSalary) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean result = false;

        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE company_employee SET position = ?, base_salary = ? WHERE id = ? AND status = 1";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, position);
            pstmt.setBigDecimal(2, baseSalary);
            pstmt.setString(3, id);

            int affectedRows = pstmt.executeUpdate();
            result = (affectedRows > 0);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }

        return result;
    }
    
    @Override
    public boolean fireEmployee(String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean result = false;

        try {
            conn = DBUtil.getConnection();
            conn.setAutoCommit(false);
            
            // 1. 获取员工信息
            String selectSql = "SELECT user_id, company_id FROM company_employee WHERE id = ? AND status = 1";
            pstmt = conn.prepareStatement(selectSql);
            pstmt.setString(1, id);
            rs = pstmt.executeQuery();
            
            if (!rs.next()) {
                return false;
            }
            
            String userId = rs.getString("user_id");
            String companyId = rs.getString("company_id");
            
            // 关闭结果集
            rs.close();
            pstmt.close();
            
            // 2. 删除员工的申请记录，而不是更新状态
            // 这样员工被开除后可以重新申请加入公司
            String deleteEmployeeSql = "DELETE FROM company_employee WHERE user_id = ? AND company_id = ?";
            pstmt = conn.prepareStatement(deleteEmployeeSql);
            pstmt.setString(1, userId);
            pstmt.setString(2, companyId);
            pstmt.executeUpdate();
            pstmt.close();
            
            // 3. 更新用户表中的公司ID和员工编号为null
            String updateUserSql = "UPDATE user SET company_id = NULL, employee_number = NULL WHERE id = ?";
            pstmt = conn.prepareStatement(updateUserSql);
            pstmt.setString(1, userId);
            pstmt.executeUpdate();
            pstmt.close();
            
            // 4. 更新公司员工数量减1
            String updateCompanySql = "UPDATE company SET employee_count = employee_count - 1 WHERE id = ? AND employee_count > 0";
            pstmt = conn.prepareStatement(updateCompanySql);
            pstmt.setString(1, companyId);
            pstmt.executeUpdate();
            
            conn.commit();
            result = true;
        } catch (SQLException e) {
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.setAutoCommit(true);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            DBUtil.close(conn, pstmt, rs);
        }

        return result;
    }

    // 辅助方法：将ResultSet映射到CompanyEmployee对象
    private CompanyEmployee mapResultSetToCompanyEmployee(ResultSet rs) throws SQLException {
        CompanyEmployee companyEmployee = new CompanyEmployee();
        companyEmployee.setId(rs.getString("id"));
        companyEmployee.setCompanyId(rs.getString("company_id"));
        companyEmployee.setUserId(rs.getString("user_id"));
        companyEmployee.setEmployeeNumber(rs.getString("employee_number"));
        companyEmployee.setPosition(rs.getString("position"));
        companyEmployee.setBaseSalary(rs.getBigDecimal("base_salary"));
        companyEmployee.setStatus(rs.getInt("status"));
        companyEmployee.setReason(rs.getString("reason"));
        companyEmployee.setRejectReason(rs.getString("reject_reason"));
        companyEmployee.setCreateTime(rs.getTimestamp("create_time"));
        companyEmployee.setUpdateTime(rs.getTimestamp("update_time"));
        
        // 尝试获取离职相关字段
        try {
            companyEmployee.setResignationReason(rs.getString("resignation_reason"));
            companyEmployee.setResignationTime(rs.getTimestamp("resignation_time"));
        } catch (SQLException e) {
            // 如果字段不存在，忽略异常
        }
        
        // 额外信息
        companyEmployee.setUserName(rs.getString("user_name"));
        companyEmployee.setUserPhone(rs.getString("user_phone"));
        companyEmployee.setCompanyName(rs.getString("company_name"));
        
        // 如果有department字段，则设置
        try {
            companyEmployee.setDepartment(rs.getString("department"));
        } catch (SQLException e) {
            // 如果没有department字段，忽略异常
        }
        
        return companyEmployee;
    }
    
    @Override
    public List<CompanyEmployee> getActiveEmployeesByCompanyId(String companyId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<CompanyEmployee> employees = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT ce.*, u.name as user_name, u.phone as user_phone, c.name as company_name " +
                    "FROM company_employee ce " +
                    "JOIN user u ON ce.user_id = u.id " +
                    "JOIN company c ON ce.company_id = c.id " +
                    "WHERE ce.company_id = ? AND ce.status = 1 " + // 只获取状态为1（在职）的员工
                    "ORDER BY ce.employee_number";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, companyId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                CompanyEmployee companyEmployee = mapResultSetToCompanyEmployee(rs);
                employees.add(companyEmployee);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return employees;
    }
    
    @Override
    public int countEmployeesByCompanyId(String companyId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int count = 0;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT COUNT(*) FROM company_employee WHERE company_id = ? AND status = 1";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, companyId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return count;
    }
    
    @Override
    public boolean submitResignation(String id, String reason) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean result = false;

        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE company_employee SET status = 3, resignation_reason = ?, resignation_time = CURRENT_TIMESTAMP " +
                         "WHERE id = ? AND status = 1";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, reason);
            pstmt.setString(2, id);

            int affectedRows = pstmt.executeUpdate();
            result = (affectedRows > 0);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }

        return result;
    }
    
    @Override
    public boolean reviewResignation(String id, int status) {
        if (status != 1 && status != 4) {
            return false; // 只允许状态1(拒绝离职)或4(批准离职)
        }
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean result = false;

        try {
            conn = DBUtil.getConnection();
            conn.setAutoCommit(false);
            
            // 1. 获取员工信息
            String selectSql = "SELECT user_id, company_id FROM company_employee WHERE id = ? AND status = 3";
            pstmt = conn.prepareStatement(selectSql);
            pstmt.setString(1, id);
            rs = pstmt.executeQuery();
            
            if (!rs.next()) {
                return false;
            }
            
            String userId = rs.getString("user_id");
            String companyId = rs.getString("company_id");
            
            rs.close();
            pstmt.close();
            
            if (status == 1) {
                // 拒绝离职，恢复在职状态
                String updateSql = "UPDATE company_employee SET status = 1 WHERE id = ?";
                pstmt = conn.prepareStatement(updateSql);
                pstmt.setString(1, id);
                pstmt.executeUpdate();
            } else if (status == 4) {
                // 批准离职，删除申请记录
                String deleteSql = "DELETE FROM company_employee WHERE id = ?";
                pstmt = conn.prepareStatement(deleteSql);
                pstmt.setString(1, id);
                pstmt.executeUpdate();
                pstmt.close();
                
                // 更新用户表中的公司ID和员工编号为null
                String updateUserSql = "UPDATE user SET company_id = NULL, employee_number = NULL WHERE id = ?";
                pstmt = conn.prepareStatement(updateUserSql);
                pstmt.setString(1, userId);
                pstmt.executeUpdate();
                pstmt.close();
                
                // 更新公司员工数量减1
                String updateCompanySql = "UPDATE company SET employee_count = employee_count - 1 WHERE id = ? AND employee_count > 0";
                pstmt = conn.prepareStatement(updateCompanySql);
                pstmt.setString(1, companyId);
                pstmt.executeUpdate();
            }
            
            conn.commit();
            result = true;
        } catch (SQLException e) {
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.setAutoCommit(true);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            DBUtil.close(conn, pstmt, rs);
        }

        return result;
    }
    
    @Override
    public CompanyEmployee getResignationByUserId(String userId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        CompanyEmployee resignation = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT ce.*, u.name as user_name, u.phone as user_phone, c.name as company_name " +
                    "FROM company_employee ce " +
                    "JOIN user u ON ce.user_id = u.id " +
                    "JOIN company c ON ce.company_id = c.id " +
                    "WHERE ce.user_id = ? AND ce.status = 3 " +
                    "ORDER BY ce.update_time DESC LIMIT 1";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                resignation = mapResultSetToCompanyEmployee(rs);
                resignation.setResignationReason(rs.getString("resignation_reason"));
                resignation.setResignationTime(rs.getTimestamp("resignation_time"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return resignation;
    }
    
    @Override
    public List<CompanyEmployee> getResignationsByCompanyId(String companyId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<CompanyEmployee> resignations = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT ce.*, u.name as user_name, u.phone as user_phone, c.name as company_name " +
                    "FROM company_employee ce " +
                    "JOIN user u ON ce.user_id = u.id " +
                    "JOIN company c ON ce.company_id = c.id " +
                    "WHERE ce.company_id = ? AND ce.status = 3 " +
                    "ORDER BY ce.resignation_time DESC";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, companyId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                CompanyEmployee resignation = mapResultSetToCompanyEmployee(rs);
                resignation.setResignationReason(rs.getString("resignation_reason"));
                resignation.setResignationTime(rs.getTimestamp("resignation_time"));
                resignations.add(resignation);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return resignations;
    }
    
    @Override
    public String getUserIdByEmployeeId(String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String userId = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT user_id FROM company_employee WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                userId = rs.getString("user_id");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return userId;
    }
    
    @Override
    public boolean cancelAllPendingApplications(String userId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean result = false;

        try {
            conn = DBUtil.getConnection();
            
            // 将用户所有待处理的申请都设置为已取消(5)
            String sql = "UPDATE company_employee SET status = 5 WHERE user_id = ? AND status = 0";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userId);
            
            pstmt.executeUpdate();
            result = true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }

        return result;
    }
} 